USE <database_name>;
GO

-- monitor the VLFs within the physical transaction file
DBCC LOGINFO('<database_name>');
GO

/*
if too many files (> many hundreds or thousands) or VLFs too large (> 500 MB)
-- then first perform a log clearing
-- (log backup or checkpoint depending on the recovery model)
*/
-- if we don't have a full backup yet, let's full backup the database before we can backup the log
BACKUP DATABASE <database_name> TO
	DISK = 'D:\MSSQL\Backup\<database_name>_Full.bak'
	WITH INIT, STATS;

-- and then the log backup
BACKUP LOG <database_name> TO
	DISK = 'D:\MSSQL\Backup\<database_name>_Log.bak'
	WITH INIT, STATS;

-- get the log file configuration
SELECT
	[file_id],
	name AS logical_name,
	physical_name,
	CAST(size/128.0 AS DECIMAL(18, 2)) AS total_size_mb,
	CAST(growth/128.0 AS DECIMAL(18, 2)) AS grow_size_mb,
	CAST((CAST(ISNULL(FILEPROPERTY(name, 'SpaceUsed'), 0) AS INT)/128.0) AS DECIMAL(18, 2)) AS space_used_mb,
	CAST(((CAST(ISNULL(FILEPROPERTY(name, 'SpaceUsed'), 0) AS INT)/128.0) / (size/128.0) * 100.0) AS DECIMAL(18, 2)) AS space_used_percent
FROM sys.database_files WHERE type = 1;

-- then shrink the log file
DBCC SHRINKFILE(N'<database_name>_log');
-- DBCC SHRINKFILE(2);
GO

-- and see the virtual log files again
DBCC LOGINFO('<database_name>');
GO

/*
if the #VLFs are still high, we may need several log backup and shrink log file operations
to successfully relocate the VLFs within the log file in order to shrink the log file
*/

-- manually grow the data file and set auto growth
ALTER DATABASE <database_name>
	MODIFY FILE (NAME = <database_name>[_dat],
	SIZE = 2048MB,
	FILEGROWTH = 1024MB);
GO

-- manually set the transaction log file to the appropriate size and set auto growth
ALTER DATABASE <database_name>
	MODIFY FILE (NAME = <database_name>_log,
	SIZE = 1024MB,
	FILEGROWTH = 512MB);
GO